# _*_ coding:utf-8 _*_
# @Time  : 2022.06.27
# @Author: zizlee
import copy
import datetime
import pathlib
import numpy as np
import pandas as pd
import openpyxl
from typing import Union
from openpyxl import styles as openpyxl_styles
from openpyxl.drawing.image import Image as ODImage
from openpyxl.drawing.xdr import XDRPositiveSize2D
from openpyxl.utils.units import pixels_to_EMU
from openpyxl.drawing.spreadsheet_drawing import AnchorMarker, OneCellAnchor
from typing import List
from fastapi import APIRouter, Query, Body
from pydantic import BaseModel, datetime_parse
from db import FAConnection
from v1_all_api.all_utils import datetime_utils
from v1_all_api.all_response import AllResponse
from global_constant import VARIETY
from configs import FILE_STORAGE, WEB_HOST

basic_api = APIRouter()


@basic_api.get('/')  # 获取基差数据
def get_futures_basic(date: str = Query(None)):
    query_ts = datetime_utils.auth_datetime_string(date, f='%Y%m%d', ts=True)
    if not query_ts:
        query_ts = datetime_utils.today_timestamp()

    query_date = datetime.datetime.fromtimestamp(query_ts)
    pre1_ts = datetime_utils.exchange_date_nearby(query_date, ts=True)
    pre1_obj = datetime.datetime.fromtimestamp(pre1_ts)
    # print(pre1_obj)
    pre2_ts = datetime_utils.exchange_date_nearby(pre1_obj + datetime.timedelta(days=-1), ts=True)
    sql = """
        SELECT t.quotes_ts,t.variety_en,t.contract,t.close_price,t.lcontract,s.price  
        FROM 
        (
            SELECT t1.quotes_ts,t1.variety_en,t1.contract,t1.close_price,t2.lcontract 
            FROM dat_futures_daily_quotes As t1 
            INNER JOIN 
            ruizy_variety_contract As t2 ON t2.activity=0 
            AND t2.contract_date=%s AND t1.contract=CONCAT(t2.variety_en,t2.lcontract) 
            WHERE t1.quotes_ts>=%s AND t1.quotes_ts<=%s
        ) As t
        INNER JOIN dat_spot_price As s ON t.variety_en=s.variety_en AND t.quotes_ts=s.spot_ts;
    """

    db_conn = FAConnection()
    records = db_conn.query(sql, param=[query_date.strftime('%Y%m%d'), pre2_ts, pre1_ts])

    df = pd.DataFrame(records)
    if df.empty:
        return AllResponse.operate_successfully(data=[])
    # 为0的现货价改为NAN
    df['price'] = df['price'].apply(lambda x: np.NAN if x == 0 else x)

    # pd.set_option('display.max_rows', None)

    df['basic_date'] = df['quotes_ts'].apply(lambda x: datetime_utils.timestamp_formatter(x))
    df.sort_values(by=['variety_en', 'basic_date'], inplace=True)
    df['basic_value'] = (df['price'] - df['close_price']).round(2)  # 基差值
    # 分组计算环比
    basic_df = pd.DataFrame()
    for variety in df.groupby(by='variety_en').groups:
        v_df = df[df['variety_en'] == variety]
        v_df['close_price_diff'] = v_df['close_price'].diff().round(2)
        v_df['price_diff'] = v_df['price'].diff().round(2)
        v_df['basic_diff'] = v_df['basic_value'].diff().round(2)
        basic_df = pd.concat([basic_df, v_df])
    # 取查询日期的数据
    basic_df = basic_df[basic_df['quotes_ts'] == pre1_ts]
    # basic_df.fillna('-', inplace=True)
    basic_df.dropna()
    basic_df['basic_rate'] = (basic_df['basic_value'] / basic_df['close_price']).round(4)
    basic_df['variety_name'] = basic_df['variety_en'].apply(lambda x: VARIETY.VARIETY_NAMES.get(x, x))
    # 处理basic_rate的INF
    basic_df['basic_rate'][np.isinf(basic_df['basic_rate'])] = 999
    # 处理basic_rate的NAN
    basic_df['basic_rate'][pd.isna(basic_df['basic_rate'])] = 999
    # basic_df.fillna('-', inplace=True)
    del basic_df['quotes_ts']
    basic_data = {bd['variety_en']: bd for bd in basic_df.to_dict(orient='records')}
    # 品种控制
    nan_spot = {'basic_date': query_date.strftime('%Y-%m-%d'), 'contract': '-', 'close_price': 9999999999,
                'lcontract': '-',
                'price': '-', 'basic_value': '-', 'close_price_diff': '-', 'price_diff': '-', 'basic_diff': '-',
                'basic_rate': 999}
    _variety = ['NI', 'CU', 'ZN', 'PB', 'AL', 'SN', 'AU', 'AG', 'SS', 'RB', 'HC', 'BU', 'RU', 'SP', 'A', 'B', 'M', 'Y',
                'P', 'LH', 'JD', 'C', 'CS', 'I', 'J', 'JM', 'SC', 'FU', 'LU', 'L', 'PP', 'V', 'EB', 'PG', 'EG',
                'PX', 'TA', 'PF', 'MA', 'SA', 'SH','FG', 'UR', 'SR', 'CF', 'CY', 'CJ', 'RM', 'OI', 'PK', 'ZC', 'SF',
                'SM', 'SI', 'LC', 'BR']
    response_data = []
    for v in _variety:
        v_basic = basic_data.get(v)
        if v_basic:
            response_data.append(v_basic)
        else:
            _nan = copy.deepcopy(nan_spot)
            _nan['variety_en'] = v
            _nan['variety_name'] = VARIETY.VARIETY_NAMES.get(v, v)
            response_data.append(_nan)
    response_data.sort(key=lambda x: x['basic_rate'], reverse=True)
    # 去掉几个不填现货的品种
    # response_data = list(filter(lambda x: x['variety_en'] not in ['FU', 'SC', 'LU', 'PG'], response_data))
    # 去掉收盘价格为0的品种
    response_data = list(filter(lambda x: x['close_price'] > 0, response_data))
    for item in response_data:
        if item['basic_rate'] == 999:
            item['basic_rate'] = 'NAN'
        if item['close_price'] == 9999999999:
            item['close_price'] = '-'
    # ret_df = pd.DataFrame(response_data)
    # ret_df.fillna('NAN', inplace=True)
    # response_data = ret_df.to_dict(orient='records')
    return AllResponse.operate_successfully(data=response_data)


class BasicDataItem(BaseModel):
    basic_date: datetime_parse.date
    variety_en: str
    variety_name: str
    price: Union[float, str]
    price_diff: Union[float, str]
    basic_value: Union[float, str]
    basic_rate: str


class BasicDataExporter(object):
    def __init__(self, basic_data: List[BasicDataItem]):
        self.basic_data = basic_data

    @staticmethod
    def cell_forward_color(color="ffffff"):
        # patternType='solid' # 纯色填充 bgColor背景色,fgColor 前景色
        return openpyxl_styles.PatternFill(patternType='solid', fgColor=color)

    @staticmethod
    def cell_alignment(horizontal=None, vertical=None, wrap_text=None):
        return openpyxl_styles.Alignment(horizontal=horizontal, vertical=vertical, wrap_text=wrap_text)

    @staticmethod
    def cell_font(size=11, color='000000', bold=False, name=None):
        return openpyxl_styles.Font(size=size, color=color, bold=bold, name=name)

    @staticmethod
    def cell_border(left=None, top=None, right=None, bottom=None,
                    l_color=None, t_color=None, r_color=None, b_color=None):
        # 边框样式，可选dashDot、dashDotDot、dashed、dotted、double、hair、medium、mediumDashDot、
        # mediumDashDotDot、mediumDashed、slantDashDot、thick、thin
        side = openpyxl_styles.Side
        # colors = openpyxl_styles.colors
        return openpyxl_styles.Border(top=side(style=top, color=t_color),
                                      left=side(style=left, color=l_color),
                                      right=side(style=right, color=r_color),
                                      bottom=side(style=bottom, color=b_color))

    @staticmethod
    def get_basic_rate_percent(rate):
        try:
            r = round(float(rate), 4)
        except ValueError:
            return 'NAN'
        return r

    def export_excel(self):
        work_book = openpyxl.Workbook()
        sheet = work_book.active
        sheet['D2'].fill = self.cell_forward_color(color='2f75b5')
        sheet.merge_cells('D2:H2')
        sheet.row_dimensions[3].height = 35
        sheet.row_dimensions[4].height = 35
        # 贴二维码
        qrcode1_path = pathlib.Path(FILE_STORAGE).joinpath('AppImages/yjyqrcode1.jpg')
        qrcode1 = ODImage(qrcode1_path)
        qrcode1.width, qrcode1.height = (82, 95)  # 这里宽高需相反，具体我也不知道为什么
        # 计算出图片在excel中位置，是图片的大小
        size = XDRPositiveSize2D(pixels_to_EMU(qrcode1.height), pixels_to_EMU(qrcode1.width))
        # 相对于单元格内的(x,y,top,left)偏移量,行从0计数，此处-1
        marker = AnchorMarker(colOff=pixels_to_EMU(5), rowOff=pixels_to_EMU(5), row=2, col=3)
        qrcode1.anchor = OneCellAnchor(_from=marker, ext=size)
        sheet.add_image(qrcode1)

        sheet['E3'].value = '期货品种主力基差表'
        # sheet['E3'].font = self.cell_font(size=18, color='2f75b5', name='微软雅黑', bold=True)
        sheet['E3'].font = self.cell_font(size=18, color='2f75b5', name='宋体', bold=True)
        sheet['E3'].alignment = self.cell_alignment(horizontal='center', vertical='center')
        sheet.merge_cells('E3:H3')
        sheet['E4'].value = '来源：瑞达期货研究院'
        # sheet['E4'].font = self.cell_font(size=9, name='等线')
        sheet['E4'].font = self.cell_font(size=9, name='宋体')
        sheet.merge_cells('E4:F4')
        sheet['E4'].alignment = self.cell_alignment(horizontal='right', vertical='center')

        basic_date = datetime.datetime.today().strftime("%Y{}%m{}%d{}").format('年', '月', '日')
        if len(self.basic_data) > 0:
            basic_date = self.basic_data[0].basic_date.strftime("%Y{}%m{}%d{}").format('年', '月', '日')
        sheet['G4'].value = f'时间：{basic_date}'
        # sheet['G4'].font = self.cell_font(size=9, name='等线')
        sheet['G4'].font = self.cell_font(size=9, name='宋体')
        sheet.merge_cells('G4:H4')
        sheet['G4'].alignment = self.cell_alignment(horizontal='center', vertical='center')

        # 去灰色边框
        for letter in ['D', 'E', 'F', 'G', 'H']:
            sheet[f'{letter}3'].fill = self.cell_forward_color(color='ffffff')
            sheet[f'{letter}4'].fill = self.cell_forward_color(color='ffffff')
        # # 补边框
        sheet['D3'].border = self.cell_border(left='thin', l_color='d4d4d4')
        sheet['D4'].border = self.cell_border(left='thin', l_color='d4d4d4')
        sheet['I3'].border = self.cell_border(left='thin', l_color='d4d4d4')
        sheet['I4'].border = self.cell_border(left='thin', l_color='d4d4d4')
        # 表头
        title_letters = ['D', 'E', 'F', 'G', 'H']
        for index, col_name in enumerate(['品种', '现货', '现货涨跌', '基差', '基差率']):
            pos = f'{title_letters[index]}5'
            sheet[pos].value = col_name
            # sheet[pos].font = self.cell_font(color='ffffff', bold=True, name='微软雅黑')
            sheet[pos].font = self.cell_font(color='ffffff', bold=True, name='宋体')
            sheet[pos].fill = self.cell_forward_color(color='2f75b5')
            sheet[pos].border = self.cell_border(left='thin', top='thin', right='thin', bottom='thin',
                                                 l_color='000000', t_color='000000', r_color='000000', b_color='000000')
            sheet[pos].alignment = self.cell_alignment(horizontal='center', vertical='center')

        for letter in title_letters:
            sheet.column_dimensions[letter].width = 11
        # 填具体数据开始
        start_row = 6

        for basic_item in self.basic_data:
            sheet[f'D{start_row}'].value = basic_item.variety_name
            # sheet[f'D{start_row}'].font = self.cell_font(size=12, color='1f4e78', bold=True, name='等线')
            sheet[f'D{start_row}'].font = self.cell_font(size=12, color='1f4e78', bold=True, name='宋体')
            sheet[f'E{start_row}'].value = basic_item.price
            # sheet[f'E{start_row}'].font = self.cell_font(name='等线')
            sheet[f'E{start_row}'].font = self.cell_font(name='宋体')
            sheet[f'F{start_row}'].value = basic_item.price_diff
            sheet[f'F{start_row}'].font = self.cell_font(name='宋体')
            sheet[f'G{start_row}'].value = basic_item.basic_value
            sheet[f'G{start_row}'].font = self.cell_font(name='宋体')
            sheet[f'H{start_row}'].value = self.get_basic_rate_percent(basic_item.basic_rate)
            sheet[f'H{start_row}'].number_format = '0.00%'
            sheet[f'H{start_row}'].font = self.cell_font(name='宋体')
            sheet[f'H{start_row}'].alignment = self.cell_alignment(horizontal='right')
            start_row += 1

        # 贴水印
        watermark = pathlib.Path(FILE_STORAGE).joinpath('AppImages/basic/basic_watermark.png')
        watermark_obj = ODImage(watermark)
        watermark_obj.width, watermark_obj.height = (270, 450)  # 这里宽高需相反，具体我也不知道为什么
        # 计算出图片在excel中位置，是图片的大小
        size = XDRPositiveSize2D(pixels_to_EMU(watermark_obj.height), pixels_to_EMU(watermark_obj.width))
        # 相对于单元格内的(x,y,top,left)偏移量,行从0计数，此处-1
        marker = AnchorMarker(colOff=pixels_to_EMU(5), rowOff=pixels_to_EMU(5), row=14, col=3)
        watermark_obj.anchor = OneCellAnchor(_from=marker, ext=size)
        sheet.add_image(watermark_obj)

        # 说明
        sheet[f'D{start_row}'].value = '说明：以上数据来源第三方，仅供参考，不作入市依据！（基差率=基差/期货价格）'
        sheet[f'D{start_row}'].font = self.cell_font(size=9, color='ff0000', name='宋体')
        temp_folder = pathlib.Path('Temp/basic/')
        save_folder = pathlib.Path(FILE_STORAGE).joinpath(temp_folder)
        filename = f'{basic_date}基差表.xlsx'
        save_file = save_folder.joinpath(filename)
        work_book.save(save_file)
        return str(pathlib.Path(temp_folder).joinpath(filename))


@basic_api.post('/exportExcel/')  # 导出Excel
async def export_basic_data_to_excel(basic_data: List[BasicDataItem] = Body(...)):
    exporter = BasicDataExporter(basic_data=basic_data)
    filepath = exporter.export_excel()
    response_data = {'file_url': WEB_HOST + filepath}
    return AllResponse.operate_successfully(data=response_data)

